﻿using BusinessEntity;
using BusinessEntity.xmglQuerryEntity;
using BusinessRepository;
using BusinessRule.Organization;
using learun.util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Dynamic;
using System.IO;
using System.Text;
using System.Threading.Tasks;

namespace BusinessRule
{
    public class ProHeTongRule
    {
        #region 合同
        /// <summary>
        /// 用户列表（导出Excel）
        /// </summary>
        /// <returns></returns>
        public static async Task<MemoryStream> GetExportList(List<string> ids, List<ColumnModel> cols)
        {
            //List<string> ids = new List<string>();
            //List<ColumnModel> cols = new List<ColumnModel>();

            //取出数据源
            DataTable exportTable = await ProHeTongRule.GetExportList(ids);
            //设置导出格式
            ExcelConfig excelconfig = new ExcelConfig();
            excelconfig.Title = "合同信息";
            excelconfig.TitleFont = "微软雅黑";
            excelconfig.TitlePoint = 25;
            //excelconfig.HeadPoint = 12;
            excelconfig.FileName = "合同信息.xls";
            excelconfig.IsAllSizeColumn = true;
            //每一列的设置,没有设置的列信息，系统将按datatable中的列名导出
            excelconfig.ColumnEntity = new List<ColumnModel>();
            for (int i = 0; i < cols.Count; i++)
            {
                excelconfig.ColumnEntity.Add(new ColumnModel() { Column = cols[i].Column, ExcelColumn = cols[i].ExcelColumn });
            }

            //excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_account", ExcelColumn = "账户" });
            //excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_realname", ExcelColumn = "姓名" });
            //excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_gender", ExcelColumn = "性别" });
            //excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_birthday", ExcelColumn = "生日" });
            //excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_mobile", ExcelColumn = "手机", Background = Color.Red });
            //excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_telephone", ExcelColumn = "电话", Background = Color.Red });
            //excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_wechat", ExcelColumn = "微信" });
            //excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_organize", ExcelColumn = "公司" });
            //excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_department", ExcelColumn = "部门" });
            //excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_description", ExcelColumn = "说明" });
            //excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_createdate", ExcelColumn = "创建日期" });
            //excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_createusername", ExcelColumn = "创建人" });
            //
            return ExcelHelper.ExportMemoryStream(exportTable, excelconfig);
        }

        public async static Task<DataTable> GetExportList(List<string> ids)
        {
            //throw new NotImplementedException();
            string yids = string.Join("','", ids);
            yids = "'" + yids + "'";
            string strsql = "select * from View_ProHTLists where dxid in ("+ yids + ")";
            return await new RepositoryFactory().BaseRepository().FindTable(strsql);
        }

        //查询列表
        //public static async Task<(DataTable, int)> GetHTIndexList(ProHeTongQuerry querry, string strdepids)
        public static async Task<(IEnumerable<dynamic>, int)> GetHTIndexList(ProHeTongQuerry querry, string strdepids)
        {
            string str = "";

            if (!(string.IsNullOrEmpty(querry.htmc)))
            {
                str = str + " and htmc like ''%" + querry.htmc + "%''";
            }
            if (!(string.IsNullOrEmpty(querry.htlx)))
            {
                str = str + " and htlx = ''" + querry.htlx + "''";
            }

            if (!(string.IsNullOrEmpty(querry.htid)))
            {
                str = str + " and id like ''%" + querry.htid + "%''";
            }
            if (!(string.IsNullOrEmpty(querry.htbh)))
            {
                str = str + " and htbh like ''%" + querry.htbh + "%''";
            }

            if (!(string.IsNullOrEmpty(querry.xmmc)))
            {
                str = str + " and xmmc like '%" + querry.xmmc + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.dxmc)))
            {
                str = str + " and dxmc like ''%" + querry.dxmc + "%''";
            }

            if (!(string.IsNullOrEmpty(querry.cgmc)))
            {
                str = str + " and cgmc like ''%" + querry.cgmc + "%''";
            }

            if (!(string.IsNullOrEmpty(querry.zbfs)))
            {
                str = str + " and zbfs = ''" + querry.zbfs + "''";
            }

            if (!(string.IsNullOrEmpty(querry.begindate.ToString())))
            {
                str = str + " and htrq >= ''" + querry.begindate + "''";
            }
            if (!(string.IsNullOrEmpty(querry.enddate.ToString())))
            {
                str = str + " and htrq <= ''" + querry.enddate + "''";
            }

            if (!(string.IsNullOrEmpty(querry.status)))
            {
                str = str + " and spstatus = ''" + querry.status + "''";
            }

            if (!(string.IsNullOrEmpty(querry.ccode)))
            {
                str = str + " and ccode like ''%" + querry.ccode + "%''";
            }
            if (!(string.IsNullOrEmpty(querry.xmid)))
            {
                str = str + " and xmid = ''" + querry.xmid + "''";
            }

            str += " and xmid not in (select xmid from CProGD)";

            //没有签合同的单项 合同申请
            if (!(string.IsNullOrEmpty(querry.has)))
            {
                str = str + " and isnull(id,'''') = ''''";
            }

            string sql = "select * from View_ProHTLists where (departid in " + strdepids + " or cqdwid in " + strdepids + ")" + str.Replace("''", "'");
            bool isAsc = true;
            if (querry.sort != "Asc" && querry.sort != "asc")
            {
                isAsc = false;
            }
            if (string.IsNullOrEmpty(querry.column))
            {
                querry.column = "zbid desc,id";
            }
            //return await new RepositoryFactory().BaseRepository().FindTable(sql, null, querry.column, isAsc, querry.size, querry.current);
            return await new RepositoryFactory().BaseRepository().FindList(sql, null, querry.column, isAsc, querry.size, querry.current);
        }

        //查询单条数据
        public static async Task<dynamic> GetHTByIdSig(string keyValue)
        {
            string sql = "select * from View_ProHTLists where id = '" + keyValue + "'";
            return await new RepositoryFactory().BaseRepository().FindEntity(sql, null);
        }

        //根据id查询单条数据
        public static async Task<IEnumerable<dynamic>> GetHTById(string keyValue)
        {
            string sql = "select * from View_ProHTLists where id = '" + keyValue + "'";
            return await new RepositoryFactory().BaseRepository().FindList(sql, null);
        }

        //判断合同编号是否重复
        public static async Task<bool> GetHTIDExits(string htid,string dxid)
        {
            string strsql = "select 1 from CProContract where htbh = '"+ htid + "' and dxid <> '"+ dxid + "'";
            DataTable dts = await new RepositoryFactory().BaseRepository().FindTable(strsql, null);
            bool exists = false;
            for (int i = 0; i < dts.Rows.Count; i++)
            {
                exists = true;
            }
            return exists;
        }

        public static async Task<bool> InsertCProContract(CProContract ht, CProContractSF htjf , List<CProContractSF> htsf, List<BConstructionUnitsYH> htskzh, List<CProConFKJH> htfkjh, List<CProConBZJ> htbzj, List<CProConFuZeRen> htfzr, string strope)
        {
            
            if (strope == "add")
            {
                return await TranstionRule.GetRunActionTransAsync(async irp =>
                {
                    //string strsql = "dele";
                    //先删除
                    //await irp.DeleteAny<CProContract>(new { dxid = ht.dxid });
                    await irp.Insert(ht);

                    htjf.lx = "jf";
                    htjf.id = CommonHelper.GetUniqueId().ToString();
                    htjf.htid = ht.id;
                    //合同甲方
                    await irp.Insert(htjf);
                    
                    //合同三方
                    for (int i = 0; i < htsf.Count; i++)
                    {
                        htsf[i].id = CommonHelper.GetUniqueId().ToString();
                        htsf[i].lx = "yf";
                        htsf[i].htid = ht.id;
                        await irp.Insert(htsf[i]);
                    }
                    //收款账号
                    for (int i = 0; i < htskzh.Count; i++)
                    {
                        htskzh[i].id = CommonHelper.GetUniqueId().ToString();
                        htskzh[i].htid = ht.id;
                        await irp.Insert(htskzh[i]);
                    }

                    //付款计划
                    for (int i = 0; i < htfkjh.Count; i++)
                    {
                        htfkjh[i].id = CommonHelper.GetUniqueId().ToString();
                        htfkjh[i].htid = ht.id;
                        await irp.Insert(htfkjh[i]);
                    }

                    //保证金
                    for (int i = 0; i < htbzj.Count; i++)
                    {
                        htbzj[i].id = CommonHelper.GetUniqueId().ToString();
                        htbzj[i].htid = ht.id;
                        htbzj[i].step = "0";//未退回操作
                        htbzj[i].bzjlog = "no";
                        await irp.Insert(htbzj[i]);
                    }

                    //负责人
                    for (int i = 0; i < htfzr.Count; i++)
                    {
                        htfzr[i].id = CommonHelper.GetUniqueId().ToString();
                        htfzr[i].htid = ht.id;
                        await irp.Insert(htfzr[i]);
                    }


                    return true;

                });
            }
            else if (strope == "edit")
            {
                return await TranstionRule.GetRunActionTransAsync(async irp =>
                {


                    //获取id
                    string tid = ht.id;
                    await irp.Update(ht);
                    //合同三方

                    string strsql = "delete from CProContractSF where htid  = '" + ht.id + "'";
                    await irp.ExecuteSql(strsql);

                    htjf.id = CommonHelper.GetUniqueId().ToString();
                    htjf.lx = "jf";
                    htjf.htid = ht.id;
                    //合同甲方
                    await irp.Insert(htjf);

                    if (null != htsf)//&& htsf.Count > 0
                    {
                        //string strsqlls = "delete from CProContractSF where htid in (select id from CProContract where dxid = '" + ht.dxid + @"')";
                        //await irp.ExecuteSql(strsqlls);
                        for (int i = 0; i < htsf.Count; i++)
                        {
                            htsf[i].id = CommonHelper.GetUniqueId().ToString();
                            htsf[i].htid = tid;
                            await irp.Insert(htsf[i]);
                        }
                    }
                    if (null != htskzh)//&& htskzh.Count > 0
                    {
                        string strsqlls = "delete from BConstructionUnitsYH where htid = '"+ ht.id + "'";
                        await irp.ExecuteSql(strsqlls);
                        //收款账号
                        for (int i = 0; i < htskzh.Count; i++)
                        {
                            htskzh[i].id = CommonHelper.GetUniqueId().ToString();
                            htskzh[i].htid = tid;
                            await irp.Insert(htskzh[i]);
                        }
                    }

                    //付款计划
                    if (null != htfkjh)// && htfkjh.Count > 0
                    {
                        string strsqlls = "delete from CProConFKJH where htid  = '" + ht.id + "'";
                        await irp.ExecuteSql(strsqlls);
                        for (int i = 0; i < htfkjh.Count; i++)
                        {
                            htfkjh[i].id = CommonHelper.GetUniqueId().ToString();
                            htfkjh[i].htid = tid;
                            await irp.Insert(htfkjh[i]);
                        }
                    }

                    //保证金
                    if (null != htbzj)//&& htbzj.Count > 0
                    {
                        string strsqlls = "delete from CProConBZJ where htid  = '" + ht.id + "'";
                        await irp.ExecuteSql(strsqlls);
                        for (int i = 0; i < htbzj.Count; i++)
                        {
                            htbzj[i].id = CommonHelper.GetUniqueId().ToString();
                            htbzj[i].htid = tid;
                            htbzj[i].step = "0";//未退回操作
                            await irp.Insert(htbzj[i]);
                        }
                    }

                    //负责人
                    if (null != htfzr)//&& htfzr.Count > 0
                    {
                        string strsqlls = "delete from CProConFuZeRen where htid  = '" + ht.id + "'";
                        await irp.ExecuteSql(strsqlls);

                        for (int i = 0; i < htfzr.Count; i++)
                        {
                            htfzr[i].id = CommonHelper.GetUniqueId().ToString();
                            htfzr[i].htid = tid;
                            await irp.Insert(htfzr[i]);
                        }
                    }


                    return true;

                });
            }
            else
                return true;
        }

        //新增修改合同
        public static async Task InsertCProContract(CProContract ht, string uid)
        {
            CProContract count = await new RepositoryFactory().BaseRepository().FindEntity<CProContract>("select * from CProContract where dxid = '" + ht.dxid + "'");
            if (count is null)
            {
                if (await GetXMISJUSAsync(ht.htbh))
                {
                    throw new Exception("项目已决算，不能操作");
                }

                string strid = CommonHelper.GetUniqueId().ToString();
                ht.id = strid;
                if (!string.IsNullOrEmpty(ht.htbh))
                {
                    string sqlhtid = "select * from CProContract where htbh = '" + ht.htbh + "'";
                    DataTable tablehtid = await new RepositoryFactory().BaseRepository().FindTable(sqlhtid, null);
                    if (tablehtid.Rows.Count == 0)
                    {
                        ht.htbh = ht.htbh;
                    }
                    else
                    {
                        //var varhtid = await new RepositoryFactory().BaseRepository().ExecuteProc<dynamic>("GetHtid");

                        //object ghtid = Newtonsoft.Json.JsonConvert.DeserializeObject<GetHtid>(varhtid.ToJson().ToString());
                        //ht.htid = varhtid.ToJson().ToObject<GetHtid>().htid;

                        tablehtid = await new RepositoryFactory().BaseRepository().FindTable("exec GetHtid");
                        for (int i = 0; i < tablehtid.Rows.Count; i++)
                        {
                            ht.htbh = tablehtid.Rows[i][0].ToString();
                        }
                    }
                }
                else
                {
                    var varhtid2 = await new RepositoryFactory().BaseRepository().ExecuteProc<GetHtid>("GetHtid");
                    //object ghtid2 = Newtonsoft.Json.JsonConvert.DeserializeObject<GetHtid>(varhtid2.ToJson().ToString());
                    //ht.htid = varhtid2.ToJson().ToObject<GetHtid>().htid;
                    ht.htbh = varhtid2.htid;
                }
                if (string.IsNullOrEmpty(ht.status))
                {
                    ht.status = "2";
                }
                if (string.IsNullOrEmpty(ht.remark2))
                {
                    ht.remark2 = "all";
                }
                ht.createuser = uid;

                await new RepositoryFactory().BaseRepository().Insert(ht);
                //乙方
                string sql = "select * from BConstructionUnits where dwmc = '" + ht.yf + "' and types ='1'";
                DataTable table = await new RepositoryFactory().BaseRepository().FindTable(sql, null);
                if (table.Rows.Count == 0)
                {
                    BConstructionUnits units = new BConstructionUnits
                    {
                        id = CommonHelper.GetUniqueId().ToString(),
                        dwmc = ht.yf,
                        types = "1",
                        hmd = "0"
                    };
                    await new RepositoryFactory().BaseRepository().Insert(units);
                }


            }
            else
            {
                if (await GetXMISJUSAsync(ht.htbh))
                {
                    throw new Exception("项目已决算，不能操作");
                }
                if (string.IsNullOrEmpty(ht.status))
                {
                    ht.status = "2";
                }
                if (string.IsNullOrEmpty(ht.remark2))
                {
                    ht.remark2 = "all";
                }
                ht.createuser = uid;

                //添加一个新方法
                await new RepositoryFactory().BaseRepository().UpdateAny(ht, new { dxid = ht.dxid }, true);
                //乙方
                string sql = "select * from BConstructionUnits where dwmc = '" + ht.yf + "' and types ='1'";
                DataTable table = await new RepositoryFactory().BaseRepository().FindTable(sql, null);
                if (table.Rows.Count == 0)
                {
                    BConstructionUnits units = new BConstructionUnits
                    {
                        id = CommonHelper.GetUniqueId().ToString(),
                        dwmc = ht.yf,
                        types = "1",
                        hmd = "0"
                    };
                    await new RepositoryFactory().BaseRepository().Insert(units);
                }
            }
        }

        public static async Task<bool> GetXMISJUSAsync(string htid)
        {
            string sql = "select * from CProJuS where htid in (select xmid from dbo.CProManageLX where id in\r\n (select dxid from dbo.CProContract where htid = '" + htid + "')) and status = '2'";
            DataTable table = await new RepositoryFactory().BaseRepository().FindTable(sql, null);
            bool flag = false;
            for (int i = 0; i < table.Rows.Count; i++)
            {
                flag = true;
            }
            return flag;
        }

        //删除多条-dxid-id1标段id,htid-id2合同编码
        public static async Task DeleteCProContractMulti(Root ids)
        {
            foreach (BaseDeleteMulti idm in ids.ids)
            {
                await new RepositoryFactory().BaseRepository().ExecuteProc("deleteHT", new { htid = idm.id });
            }

        }

        //合同甲方
        public static async Task<IEnumerable<dynamic>> GetHTJF(string name)
        {
            string sql = "select top 10 id,bmmc,frdb,dbry,fddz,yzbh,shxydm,sfz,lxdh from BGlbmInfo where isjsdw = '1' and bmmc like '%" + name + "%' order by id desc";
            if (string.IsNullOrEmpty(name))
                sql = "select top 10 id,bmmc,frdb,dbry,fddz,yzbh,shxydm,sfz,lxdh from BGlbmInfo where isjsdw = '1' order by id desc";
            return await new RepositoryFactory().BaseRepository().FindList(sql, null);
        }

        public static async Task<IEnumerable<dynamic>> GetHTJFNew(string name)
        {
            string sql = "select top 10 id,bmmc,frdb,dbry,fddz,yzbh,shxydm,sfz,lxdh from BGlbmInfo where isjsdw = '1' and bmmc like '%" + name + "%' order by id desc";
            if (string.IsNullOrEmpty(name))
                sql = "select top 10 id,bmmc,frdb,dbry,fddz,yzbh,shxydm,sfz,lxdh from BGlbmInfo where isjsdw = '1' order by id desc";
            return await new RepositoryFactory().BaseRepository().FindList(sql, null);
        }

        //合同乙方
        public static async Task<(IEnumerable<dynamic>, int)> GetHTYF(string name, string current, string size, string sort, string column)
        {
            string sql = @"select * from (
select ROW_NUMBER() over(partition by dwmc order by id desc)rr,id,dwmc,
frdb,dbry,fddz,yzbh,shxydm,sfz,lxdh from CProContractSF where lx = 'yf')t where rr = '1' and dwmc like '%" + name + "%' ";
            
            if (string.IsNullOrEmpty(name))
                sql = @"select * from (
select ROW_NUMBER() over(partition by dwmc order by id desc)rr,id,dwmc,
frdb,dbry,fddz,yzbh,shxydm,sfz,lxdh from CProContractSF where lx = 'yf')t where rr = '1'";
 

            bool isAsc = sort.ToLower() == "asc" ? false : true;
            
            if (string.IsNullOrEmpty(column))
            {
                column = "id";
            }

            return await new RepositoryFactory().BaseRepository().FindList(sql, null, column, isAsc, size.ToInt(), current.ToInt());

        }

        public static async Task<IEnumerable<dynamic>> GetHTSKYHByYF(string name)
        {
            string sql = @"select * from BConstructionUnitsYH where htid in(
select id from CProContract where yf = '" + name + "')";


            return await new RepositoryFactory().BaseRepository().FindList(sql, null);
        }

        //合同全部信息列表
        public static async Task<dynamic> GetHTAllInfo(string tid)
        {
            dynamic d = new ExpandoObject();
            string strsql = "select * from View_ProHTLists where id = '" + tid + "'";
            d.ht = await new RepositoryFactory().BaseRepository().FindEntity(strsql);

            strsql = "select * from CProContractSF where htid = '" + tid + "' and lx = 'jf'";
            d.htjf = await new RepositoryFactory().BaseRepository().FindEntity(strsql);

            strsql = "select * from CProContractSF where htid = '" + tid + "' and lx = 'yf'";
            d.htsf = await new RepositoryFactory().BaseRepository().FindList(strsql);
            strsql = "select * from BConstructionUnitsYH where htid = '" + tid + "'";
            d.htskzh = await new RepositoryFactory().BaseRepository().FindList(strsql);
            strsql = "select * from CProConFKJH where htid = '" + tid + "'";
            d.htfkjh = await new RepositoryFactory().BaseRepository().FindList(strsql);
            strsql = "select * from CProConBZJ where htid = '" + tid + "'";
            d.htbzj = await new RepositoryFactory().BaseRepository().FindList(strsql);
            strsql = "select * from CProConFuZeRen where htid = '" + tid + "'";
            d.htfzr = await new RepositoryFactory().BaseRepository().FindList(strsql);

            //变更
            strsql = "select * from View_ProHTBGLists where htid = '" + tid + "'";
            d.htbg = await new RepositoryFactory().BaseRepository().FindList(strsql);

            //变更协议
            strsql = "select * from CProConBGxy where htid = '" + tid + "'";
            d.htbgxy = await new RepositoryFactory().BaseRepository().FindList(strsql);

            //标段信息
            //strsql = "select* from View_ProHTLists where htid = '" + tid + "'";  

            //合同

            return d;
        }

        //合同付款计划列表
        public static async Task<IEnumerable<dynamic>> GetHTFKJHList(string tid)
        {
            string strsql = "select *,(case when status = '3' then '按时间' when status ='1' then '按节点' end)ztmc from CProConFKJH where htid = '" + tid + "'";
            return await new RepositoryFactory().BaseRepository().FindList(strsql);
        }

        /// <summary>
        /// 保证金
        /// </summary>
        /// <param name="tid"></param>
        /// <returns></returns>
        public static async Task<IEnumerable<dynamic>> GetHTBZJList(string tid)
        {
            string strsql = strsql = @"select *,(case 
when ISNULL(status,-1) = '-1' then 'draft'
when ISNULL(status,-1) = '0' then 'uncheck'
when ISNULL(status,-1) = '1' then 'checking'
when ISNULL(status,-1) = '2' then 'checked'
when ISNULL(status,-1)  = '3' then 'reject'
when ISNULL(status,-1)  = '4' then 'invalid'
when ISNULL(status,-1)  = '5' then 'cancel' end
)spstatus from CProConBZJ  where htid = '" + tid + "'";
            return await new RepositoryFactory().BaseRepository().FindList(strsql);
        }


        public static async Task<bool> UpdateConBZJ(CProConBZJ bzj)
        {
            try
            {
                await new RepositoryFactory().BaseRepository().Update(bzj);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        #endregion

        #region 合同变更申请记录
        //查询列表
        //public static async Task<(DataTable, int)> GetHTIndexList(ProHeTongQuerry querry, string strdepids)
        public static async Task<(IEnumerable<dynamic>, int)> GetHTBGSQLogIndexList(ProHeTongBGSQQuerry querry, string strdepids)
        {
            string str = "";

            if (!(string.IsNullOrEmpty(querry.htmc)))
            {
                str = str + " and htmc like ''%" + querry.htmc + "%''";
            }
            if (!(string.IsNullOrEmpty(querry.htlx)))
            {
                str = str + " and htlx = ''" + querry.htlx + "''";
            }

            if (!(string.IsNullOrEmpty(querry.htid)))
            {
                str = str + " and id like ''%" + querry.htid + "%''";
            }
            if (!(string.IsNullOrEmpty(querry.htbh)))
            {
                str = str + " and htbh like ''%" + querry.htbh + "%''";
            }

            if (!(string.IsNullOrEmpty(querry.xmmc)))
            {
                str = str + " and xmmc like '%" + querry.xmmc + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.dxmc)))
            {
                str = str + " and dxmc like ''%" + querry.dxmc + "%''";
            }

            if (!(string.IsNullOrEmpty(querry.cgmc)))
            {
                str = str + " and cgmc like ''%" + querry.cgmc + "%''";
            }

            if (!(string.IsNullOrEmpty(querry.zbfs)))
            {
                str = str + " and zbfs = ''" + querry.zbfs + "''";
            }

            if (!(string.IsNullOrEmpty(querry.begindate.ToString())))
            {
                str = str + " and htrq >= ''" + querry.begindate + "''";
            }
            if (!(string.IsNullOrEmpty(querry.enddate.ToString())))
            {
                str = str + " and htrq <= ''" + querry.enddate + "''";
            }

            if (!(string.IsNullOrEmpty(querry.status)))
            {
                str = str + " and spstatus = ''" + querry.status + "''";
            }

            if (!(string.IsNullOrEmpty(querry.ccode)))
            {
                str = str + " and ccode like ''%" + querry.ccode + "%''";
            }
            if (!(string.IsNullOrEmpty(querry.xmid)))
            {
                str = str + " and xmid = ''" + querry.xmid + "''";
            }

            str += " and xmid not in (select xmid from CProGD)";

            //没有签合同的单项 合同申请
            if (!(string.IsNullOrEmpty(querry.has)))
            {
                str = str + " and isnull(id,'''') = ''''";
            }

            string sql = "select * from View_ProHTBGSQLogLists where (departid in " + strdepids + " or cqdwid in " + strdepids + ")" + str.Replace("''", "'");
            bool isAsc = true;
            if (querry.sort != "Asc" && querry.sort != "asc")
            {
                isAsc = false;
            }
            if (string.IsNullOrEmpty(querry.column))
            {
                querry.column = "zbid desc,id";
            }
           
            return await new RepositoryFactory().BaseRepository().FindList(sql, null, querry.column, isAsc, querry.size, querry.current);
        }

        //合同全部信息列表
        public static async Task<dynamic> GetHTBGSQAllInfo(string tid)
        {
            dynamic d = new ExpandoObject();
            string strsql = "select * from View_ProHTBGSQLogLists where id = '" + tid + "'";
            d.ht = await new RepositoryFactory().BaseRepository().FindEntity(strsql);

            strsql = "select * from CProContractSF where htid = '" + tid + "' and lx = 'jf'";
            d.htjf = await new RepositoryFactory().BaseRepository().FindEntity(strsql);

            strsql = "select * from CProContractSF where htid = '" + tid + "' and lx = 'yf'";
            d.htsf = await new RepositoryFactory().BaseRepository().FindList(strsql);
            strsql = "select * from BConstructionUnitsYH where htid = '" + tid + "'";
            d.htskzh = await new RepositoryFactory().BaseRepository().FindList(strsql);
            strsql = "select * from CProConFKJH where htid = '" + tid + "'";
            d.htfkjh = await new RepositoryFactory().BaseRepository().FindList(strsql);
            strsql = "select * from CProConBZJ where htid = '" + tid + "'";
            d.htbzj = await new RepositoryFactory().BaseRepository().FindList(strsql);
            strsql = "select * from CProConFuZeRen where htid = '" + tid + "'";
            d.htfzr = await new RepositoryFactory().BaseRepository().FindList(strsql);

            //xmmc,dxmc,zbdw,BuildUnit
            strsql = @"select * from View_ProHTLists
where id in (select htid from CProContractbglog where id = '" + tid + "')";
            d.xmxx = await new RepositoryFactory().BaseRepository().FindList(strsql);

            //变更
            //strsql = "select * from View_ProHTBGLists where htid = '" + tid + "'";
            //d.htbg = await new RepositoryFactory().BaseRepository().FindList(strsql);

            //变更协议
            //strsql = "select * from CProConBGxy where htid = '" + tid + "'";
            //d.htbgxy = await new RepositoryFactory().BaseRepository().FindList(strsql);

            return d;
        }

        public static async Task<bool> InsertCProContractBGSQ(CProContractbglog ht, CProContractSF htjf, List<CProContractSF> htsf, List<BConstructionUnitsYH> htskzh, List<CProConFKJH> htfkjh, List<CProConBZJ> htbzj, List<CProConFuZeRen> htfzr, string strope)
        {

            if (strope == "add")
            {
                return await TranstionRule.GetRunActionTransAsync(async irp =>
                {
                    //string strsql = "dele";
                    //先删除
                    //await irp.DeleteAny<CProContract>(new { dxid = ht.dxid });
                    await irp.Insert(ht);

                    htjf.lx = "jf";
                    htjf.id = CommonHelper.GetUniqueId().ToString();
                    htjf.htid = ht.id;
                    //合同甲方
                    await irp.Insert(htjf);

                    //合同三方
                    for (int i = 0; i < htsf.Count; i++)
                    {
                        htsf[i].id = CommonHelper.GetUniqueId().ToString();
                        htsf[i].lx = "yf";
                        htsf[i].htid = ht.id;
                        await irp.Insert(htsf[i]);
                    }
                    //收款账号
                    for (int i = 0; i < htskzh.Count; i++)
                    {
                        htskzh[i].id = CommonHelper.GetUniqueId().ToString();
                        htskzh[i].htid = ht.id;
                        await irp.Insert(htskzh[i]);
                    }

                    //付款计划
                    for (int i = 0; i < htfkjh.Count; i++)
                    {
                        htfkjh[i].id = CommonHelper.GetUniqueId().ToString();
                        htfkjh[i].htid = ht.id;
                        await irp.Insert(htfkjh[i]);
                    }

                    //保证金
                    for (int i = 0; i < htbzj.Count; i++)
                    {
                        htbzj[i].id = CommonHelper.GetUniqueId().ToString();
                        htbzj[i].htid = ht.id;
                        htbzj[i].step = "0";//未退回操作
                        htbzj[i].bzjlog = "log";
                        await irp.Insert(htbzj[i]);
                    }

                    //负责人
                    for (int i = 0; i < htfzr.Count; i++)
                    {
                        htfzr[i].id = CommonHelper.GetUniqueId().ToString();
                        htfzr[i].htid = ht.id;
                        await irp.Insert(htfzr[i]);
                    }


                    return true;

                });
            }
            else if (strope == "edit")
            {
                return await TranstionRule.GetRunActionTransAsync(async irp =>
                {


                    //获取id
                    string tid = ht.id;
                    await irp.Update(ht);
                    //合同三方

                    string strsql = "delete from CProContractSF where htid in (select id from CProContract where dxid = '" + ht.dxid + @"')";
                    await irp.ExecuteSql(strsql);

                    htjf.id = CommonHelper.GetUniqueId().ToString();
                    htjf.lx = "jf";
                    htjf.htid = ht.id;
                    //合同甲方
                    await irp.Insert(htjf);

                    if (null != htsf)//&& htsf.Count > 0
                    {
                        //string strsqlls = "delete from CProContractSF where htid in (select id from CProContract where dxid = '" + ht.dxid + @"')";
                        //await irp.ExecuteSql(strsqlls);
                        for (int i = 0; i < htsf.Count; i++)
                        {
                            htsf[i].id = CommonHelper.GetUniqueId().ToString();
                            htsf[i].htid = tid;
                            await irp.Insert(htsf[i]);
                        }
                    }
                    if (null != htskzh)//&& htskzh.Count > 0
                    {
                        string strsqlls = "delete from BConstructionUnitsYH where htid in (select id from CProContract where dxid = '" + ht.dxid + @"')";
                        await irp.ExecuteSql(strsqlls);
                        //收款账号
                        for (int i = 0; i < htskzh.Count; i++)
                        {
                            htskzh[i].id = CommonHelper.GetUniqueId().ToString();
                            htskzh[i].htid = tid;
                            await irp.Insert(htskzh[i]);
                        }
                    }

                    //付款计划
                    if (null != htfkjh)// && htfkjh.Count > 0
                    {
                        string strsqlls = "delete from CProConFKJH where htid in (select id from CProContract where dxid = '" + ht.dxid + @"')";
                        await irp.ExecuteSql(strsqlls);
                        for (int i = 0; i < htfkjh.Count; i++)
                        {
                            htfkjh[i].id = CommonHelper.GetUniqueId().ToString();
                            htfkjh[i].htid = tid;
                            await irp.Insert(htfkjh[i]);
                        }
                    }

                    //保证金
                    if (null != htbzj)//&& htbzj.Count > 0
                    {
                        string strsqlls = "delete from CProConBZJ where htid in (select id from CProContract where dxid = '" + ht.dxid + @"')";
                        await irp.ExecuteSql(strsqlls);
                        for (int i = 0; i < htbzj.Count; i++)
                        {
                            htbzj[i].id = CommonHelper.GetUniqueId().ToString();
                            htbzj[i].htid = tid;
                            htbzj[i].step = "0";//未退回操作
                            await irp.Insert(htbzj[i]);
                        }
                    }

                    //负责人
                    if (null != htfzr)//&& htfzr.Count > 0
                    {
                        string strsqlls = "delete from CProConFuZeRen where htid in (select id from CProContract where dxid = '" + ht.dxid + @"')";
                        await irp.ExecuteSql(strsqlls);

                        for (int i = 0; i < htfzr.Count; i++)
                        {
                            htfzr[i].id = CommonHelper.GetUniqueId().ToString();
                            htfzr[i].htid = tid;
                            await irp.Insert(htfzr[i]);
                        }
                    }


                    return true;

                });
            }
            else
                return true;
        }

        //删除多条-dxid-id1标段id,htid-id2合同编码
        public static async Task DeleteCProContractBGSQMulti(Root ids)
        {
            foreach (BaseDeleteMulti idm in ids.ids)
            {
                await new RepositoryFactory().BaseRepository().ExecuteProc("deleteHTBGSQ", new { htid = idm.id });
            }

        }
        #endregion

        #region 合同变更

        //查询选择合同列表
        //public static async Task<(DataTable, int)> GetHTIndexList(ProHeTongQuerry querry, string strdepids)
        public static async Task<(IEnumerable<dynamic>, int)> GetXZHTIndexList(ProHeTongQuerry querry, string strdepids)
        {
            string str = "";

            if (!(string.IsNullOrEmpty(querry.htmc)))
            {
                str = str + " and htmc like ''%" + querry.htmc + "%''";
            }

            if (!(string.IsNullOrEmpty(querry.htid)))
            {
                str = str + " and id like ''%" + querry.htid + "%''";
            }
            if (!(string.IsNullOrEmpty(querry.htbh)))
            {
                str = str + " and htbh like ''%" + querry.htbh + "%''";
            }
            
            if (!(string.IsNullOrEmpty(querry.xmmc)))
            {
                str = str + " and xmmc like '%" + querry.xmmc + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.dxmc)))
            {
                str = str + " and dxmc like ''%" + querry.dxmc + "''";
            }

            //str += " and status = '2'";

            str += " and xmid not in (select xmid from CProGD)";

            string sql = "select * from View_ProFKLists where (departid in " + strdepids + " or cqdwid in " + strdepids + ")" + str.Replace("''", "'");
            
            bool isAsc = false;
            if (querry.sort == "Asc" || querry.sort == "asc")
            {
                isAsc = true;
            }

            if (string.IsNullOrEmpty(querry.column))
            {
                querry.column = "id";
            }
            //return await new RepositoryFactory().BaseRepository().FindTable(sql, null, querry.column, isAsc, querry.size, querry.current);
            return await new RepositoryFactory().BaseRepository().FindList(sql, null, querry.column, isAsc, querry.size, querry.current);
        }


        //查询项目变更列表
        public static async Task<(IEnumerable<dynamic>, int)> GetHTBGList(CProConBGQuerry querry, string strdepids)//string uid
        {
            string str = "";
            if (!(string.IsNullOrEmpty(querry.Xmmc)))
            {
                str = str + " and xmmc like '%" + querry.Xmmc + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.Jsdw)))
            {
                str = str + " and BuildUnit like ''%" + querry.Jsdw + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.Begindate.ToString())))
            {
                str = str + " and sqrq >= ''" + querry.Begindate + "''";
            }
            if (!(string.IsNullOrEmpty(querry.Enddate.ToString())))
            {
                str = str + " and sqrq <= ''" + querry.Enddate + "''";
            }
            if (!(string.IsNullOrEmpty(querry.Status)))
            {
                str = str + " and spstatus = ''" + querry.Status + "''";
            }
            if (!(string.IsNullOrEmpty(querry.bgmc)))
            {
                str = str + " and bgmc like '%" + querry.bgmc + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.bglx)))
            {
                str = str + " and bglx = '" + querry.bglx + "'";
            }
            if (!(string.IsNullOrEmpty(querry.xmid)))
            {
                str = str + " and xmid = '" + querry.xmid + "'";
            }
         

            //string sql = "select * from(select *,(select xmmc from CProManage where id = (select xmid from dbo.CProManageLX where id = (select dxid from CProContract where CProConBG.htid = CProContract.htid))) xmmc,(select BuildUnit from CProManage where id = (select xmid from dbo.CProManageLX where id = (select dxid from CProContract where CProConBG.htid = CProContract.htid))) BuildUnit,(select departid from CProManage where id = (select xmid from dbo.CProManageLX where id = (select dxid from CProContract where CProConBG.htid = CProContract.htid))) departid,(select dxmc from dbo.CProManageLX where id = (select dxid from CProContract where CProConBG.htid = CProContract.htid)) dxmc from CProConBG ) t where departid in " + await UserRule.GetUserDepid(uid) + str.Replace("''", "'");

            //await UserRule.GetUserDepid(uid)

            str += " and xmid not in (select xmid from CProGD)";

            string sql = "select * from View_ProHTBGLists where (departid in " + strdepids + " or cqdwid in " + strdepids + ")" + str.Replace("''", "'");

            bool isAsc = false;
            if (querry.sort == "Asc" || querry.sort == "asc")
            {
                isAsc = true;
            }

            if (string.IsNullOrEmpty(querry.column))
            {
                querry.column = "id";
            }
            return await new RepositoryFactory().BaseRepository().FindList(sql, null, querry.column, isAsc, querry.size, querry.current);

        }

        public async static Task<DataTable> GetHTBGListByhtid(string tid)
        {
            string strsql = "select * from View_ProHTBGLists where htid = '" + tid + "'";

            return await new RepositoryFactory().BaseRepository().FindTable(strsql);
        }
        public async static Task<IEnumerable<dynamic>> GetHTBGByid(string bgid)
        {

            string strsql = "select * from View_ProHTBGLists where id = '" + bgid + "'";

         
            return await new RepositoryFactory().BaseRepository().FindList<dynamic>(strsql);

        }

        public async static Task<BGinfo> GetHTBGJEById(string htid,string bgid)
        {
            
            string strsql = "select * from View_ProHTBGLists where id = '" + bgid + "'";

            string strwherebgid = "";
            if(!string.IsNullOrEmpty(bgid))
            {
                strwherebgid += " ls.id <> '"+ bgid + "' and";
            }
            strsql = @"select 
ISNULL((select SUM(bgsdje) from CProConBG ls where 
"+ strwherebgid + @" ls.htid = '"+ htid + @"' and status in('1','2')),0) bgsdje,
ISNULL((select SUM(sqje) from CProConBG ls where 
" + strwherebgid + " ls.htid = '"+ htid + @"' and status in('0','1','2') and isnull(bgsdje,0)=0),0) sqje";

            return await new RepositoryFactory().BaseRepository().FindEntity<BGinfo>(strsql);

        }


        public async static Task<int> InsertHTBG(CProConBG htbg, string ope)
        {
            if (ope == "add")
            {


                return await new RepositoryFactory().BaseRepository().Insert(htbg);
            }
            else// (ope == "edit")
            {
                return await new RepositoryFactory().BaseRepository().Update(htbg);
            }

        }

        public async static Task<bool> DeleteHTBG(Root ids)
        {
            return await TranstionRule.GetRunActionTransAsync(async irp =>
            {
                foreach (BaseDeleteMulti idm in ids.ids)
                {
                    await irp.DeleteAny<CProConBG>(new { id = idm.id });


                }
                return true;
            });

            //return await new RepositoryFactory().BaseRepository().DeleteAny<CProConBG>(new { id = bgid });
        }


        //合同付款计划
        public static async Task<IEnumerable<dynamic>> GetHTFKJH(string htid)
        {
            string sql = "select *,(select dxmc from dbo.CProManageLX where id = (select dxid from CProContract where CProContract.id = CProConFKJH.htid))dxmc,convert(varchar(50), jdnum) + jdunit sjys,(select htje from CProContract where CProContract.id = CProConFKJH.htid) htje from CProConFKJH where htid = '" + htid + "'";
            return await new RepositoryFactory().BaseRepository().FindList(sql, null);
        }

        //合同收款银行信息
        public static async Task<IEnumerable<dynamic>> GetHTSKYH(string htid)
        {
            //CProConFuZeRen
            string sql = "select id,htid,yhmc,yhzh,nsrsbh,lxr,lxdh from BConstructionUnitsYH where htid = '" + htid + "'";
            
            
            return await new RepositoryFactory().BaseRepository().FindList(sql, null);
        }

        //合同收款银行信息
        public static async Task<IEnumerable<dynamic>> GetHTSKYHN(string htid,string sqid)
        {
            //CProConFuZeRen
            //string sql = "select id,htid,yhmc,yhzh,nsrsbh,lxr,lxdh from BConstructionUnitsYH where htid = '" + htid + "'";

            string strsql = "select id,htid,yhmc,yhzh,nsrsbh,lxr,lxdh from BConstructionUnitsYH where htid = '" + htid + "'";

            if (!string.IsNullOrEmpty(sqid))
            {
                strsql = @"
select distinct yhmc,yhzh from (
select id,htid,yhmc,yhzh,nsrsbh,lxr,lxdh from BConstructionUnitsYH 
where htid = (select htid from CProFKSQ where id = '" + sqid + @"')
union all
select '' id,htid,skyh,skzh ,'' ,'' ,''  from CProFKSQ
where id = '" + sqid + "')t ";
            }

            return await new RepositoryFactory().BaseRepository().FindList(strsql, null);
        }

        //合同收款银行信息
        public static async Task<IEnumerable<dynamic>> GetHTNMGSKYHN(string htid)
        {
            string strsql = @"select distinct nmggzyh,nmggzzh from CProFKSQ
where htid = '"+ htid +"' and ISNULL(nmggzyh,'')<> '' and ISNULL(nmggzzh,'')<> ''";

//            if (!string.IsNullOrEmpty(sqid))
//            {
//                strsql = @"
//select distinct yhmc,yhzh from (
//select id,htid,yhmc,yhzh,nsrsbh,lxr,lxdh from BConstructionUnitsYH 
//where htid = (select htid from CProFKSQ where id = '" + sqid + @"')
//union all
//select '' id,htid,skyh,skzh ,'' ,'' ,''  from CProFKSQ
//where id = '" + sqid + "')t ";
//            }

            return await new RepositoryFactory().BaseRepository().FindList(strsql, null);
        }

        public static async Task<IEnumerable<dynamic>> GetHTFuZeRen(string htid)
        {
            //CProConFuZeRen
            string sql = "select * from CProConFuZeRen where htid = '" + htid + "'";
            return await new RepositoryFactory().BaseRepository().FindList(sql, null);
        }

        #endregion

        #region 变更协议

        //查询项目变更列表
        public static async Task<(IEnumerable<dynamic>, int)> GetHTBGXYList(dynamic querry, string strdepids)//string uid
        {

            //基础属性
            string size= querry.size ;
            string current = querry.current ;
            string sort = querry.sort ;
            string column = querry.column ;

            string xmmc = querry.xmmc;
            string dxmc = querry.dxmc ;

            string htmc = querry.htmc ;
            string xymc = querry.xymc;

            string dateRange = querry.dateRange ;
            string status = querry.status;

            string ccode = querry.ccode;

            string str = "";
            if (!(string.IsNullOrEmpty(xmmc)))
            {
                str = str + " and xmmc like '%" + xmmc + "%'";
            }
            if (!(string.IsNullOrEmpty(dxmc)))
            {
                str = str + " and dxmc like ''%" + dxmc + "%'";
            }
            if (!(string.IsNullOrEmpty(htmc)))
            {
                str = str + " and htmc like '%" + htmc + "%'";
            }
            if (!(string.IsNullOrEmpty(xymc)))
            {
                str = str + " and xymc like '%" + xymc + "%'";
            }

            if (!(string.IsNullOrEmpty(dateRange)))
            {
                string[] strdate = dateRange.Split('_');
                if (strdate.Length > 1)
                {
                    str = str + " and xyrq >= '" + strdate[0] + "'";
                    str = str + " and xyrq <= '" + strdate[1] + "'";
                }
            }
            if (!(string.IsNullOrEmpty(ccode)))
            {
                str = str + " and ccode like ''%" + ccode + "%''";
            }

            if (!(string.IsNullOrEmpty(status)))
            {
                str = str + " and spstatus = ''" + status + "''";
            }


            str += " and xmid not in (select xmid from CProGD)";

            string sql = "select * from View_ProHTBGXYLists where (departid in " + strdepids + " or cqdwid in " + strdepids + ")" + str.Replace("''", "'");

            //bool isAsc = false;
            //if (querry.sort == "Asc" || querry.sort == "asc")
            //{
            //    isAsc = true;
            //}

            bool isAsc = sort.ToLower() == "asc" ? true : false;

            if (string.IsNullOrEmpty(column))
            {
                column = "id";
            }
            return await new RepositoryFactory().BaseRepository().FindList(sql, null, column, isAsc, size.ToInt(), current.ToInt());

        }

        public async static Task<DataTable> GetHTBGXYListByhtid(string strhtid)
        {
            string strsql = "select * from CProConBGxy where htid = '" + strhtid + "'";

            return await new RepositoryFactory().BaseRepository().FindTable(strsql);
        }


        public async static Task<IEnumerable<dynamic>> GetHTBGXYByid(string bgid)
        {
            string strsql = "select * from View_ProHTBGXYLists where id = '" + bgid + "'";
            return await new RepositoryFactory().BaseRepository().FindList(strsql);
        }

        //public async static Task<DataTable> GetHTBGXYxmByid(string bgid)
        //{
        //    string strsql = "select * from View_ProHTBGXYLists where id = '" + bgid + "'";
        //    return await new RepositoryFactory().BaseRepository().FindTable(strsql);
        //}

        public async static Task<int> InsertHTBGXY(CProConBGxy htbg, string ope)
        {
            if (ope == "add")
            {
                //htbg.status = BaseEditPower.GetSPEnumValue("待提交");

                return await new RepositoryFactory().BaseRepository().Insert(htbg);
            }
            else// (ope == "edit")
            {
                return await new RepositoryFactory().BaseRepository().Update(htbg);
            }

        }

        public async static Task<bool> DeleteHTBGXY(Root ids)
        {
            return await TranstionRule.GetRunActionTransAsync(async irp =>
            {
                foreach (BaseDeleteMulti idm in ids.ids)
                {
                    await irp.DeleteAny<CProConBGxy>(new { id = idm.id });


                }
                return true;
            });

            //return await new RepositoryFactory().BaseRepository().DeleteAny<CProConBG>(new { id = bgid });
        }

        public async static Task<dynamic> GetHTBGScale(CProConBG htbg)
        {
            string strsql = @"
select *,CONVERT(decimal(18,2),bgje*100.00 /htje)bl from (
select htje,ISNULL(
(select SUM(ISNULL(bgsdje, 0)) from CProConBG where htid = CProContract.id
and id <> '" + htbg.id + @"' and status in ('1', '2')
),0)+ISNULL(" + htbg.sqje + ", 0) bgje from CProContract where id = '" + htbg.htid + "')t ";
            return await new RepositoryFactory().BaseRepository().FindEntity(strsql);
        }

        #endregion

        #region 变更设计

        //查询项目变更列表
        public static async Task<(IEnumerable<dynamic>, int)> GetHTBGDesignList(CProConBGsjQuerry querry, string strdepids)//string uid
        {
            string str = "";
            if (!(string.IsNullOrEmpty(querry.Xmmc)))
            {
                str = str + " and xmmc like '%" + querry.Xmmc + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.Jsdw)))
            {
                str = str + " and BuildUnit like ''%" + querry.Jsdw + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.Begindate.ToString())))
            {
                str = str + " and sqrq >= ''" + querry.Begindate + "''";
            }
            if (!(string.IsNullOrEmpty(querry.Enddate.ToString())))
            {
                str = str + " and sqrq <= ''" + querry.Enddate + "''";
            }
            if (!(string.IsNullOrEmpty(querry.Status)))
            {
                str = str + " and spstatus = ''" + querry.Status + "''";
            }
            if (!(string.IsNullOrEmpty(querry.bgmc)))
            {
                str = str + " and bgmc like '%" + querry.bgmc + "%'";
            }

            //变更状态
            if (!(string.IsNullOrEmpty(querry.bgzt)))
            {
                str = str + " and bgzt = '" + querry.bgzt + "'";
            }

            //string sql = "select * from(select *,(select xmmc from CProManage where id = (select xmid from dbo.CProManageLX where id = (select dxid from CProContract where CProConBG.htid = CProContract.htid))) xmmc,(select BuildUnit from CProManage where id = (select xmid from dbo.CProManageLX where id = (select dxid from CProContract where CProConBG.htid = CProContract.htid))) BuildUnit,(select departid from CProManage where id = (select xmid from dbo.CProManageLX where id = (select dxid from CProContract where CProConBG.htid = CProContract.htid))) departid,(select dxmc from dbo.CProManageLX where id = (select dxid from CProContract where CProConBG.htid = CProContract.htid)) dxmc from CProConBG ) t where departid in " + await UserRule.GetUserDepid(uid) + str.Replace("''", "'");

            //await UserRule.GetUserDepid(uid)

            str += " and xmid not in (select xmid from CProGD)";

            string sql = "select * from View_ProHTBGSJLists where (departid in " + strdepids + " or cqdwid in " + strdepids + ")" + str.Replace("''", "'");

            bool isAsc = false;
            if (querry.sort == "Asc" || querry.sort == "asc")
            {
                isAsc = true;
            }

            if (string.IsNullOrEmpty(querry.column))
            {
                querry.column = "id";
            }
            return await new RepositoryFactory().BaseRepository().FindList(sql, null, querry.column, isAsc, querry.size, querry.current);

        }

        public async static Task<DataTable> GetHTBGDesignListByhtidAudited(string tid)
        {
            string strsql = "select * from View_ProHTBGSJLists where htid = '" + tid + "' and status = '2'";

            return await new RepositoryFactory().BaseRepository().FindTable(strsql);
        }

        public async static Task<DataTable> GetHTBGDesignListByhtid(string tid)
        {
            string strsql = "select * from View_ProHTBGSJLists where htid = '" + tid + "'";

            return await new RepositoryFactory().BaseRepository().FindTable(strsql);
        }


        public async static Task<dynamic> GetHTBGDesignByid(string bgid)
        {
            string strsql = "select * from View_ProHTBGSJLists where id = '" + bgid + "'";

            return await new RepositoryFactory().BaseRepository().FindEntity(strsql);

        }

        public async static Task<int> InsertHTBGDesign(CProConBGSJ htbg, string ope)
        {
            if (ope == "add")
            {


                return await new RepositoryFactory().BaseRepository().Insert(htbg);
            }
            else// (ope == "edit")
            {
                return await new RepositoryFactory().BaseRepository().Update(htbg);
            }

        }

        public async static Task<bool> DeleteHTBGDesign(Root ids)
        {
            return await TranstionRule.GetRunActionTransAsync(async irp =>
            {
                foreach (BaseDeleteMulti idm in ids.ids)
                {
                    await irp.DeleteAny<CProConBGSJ>(new { id = idm.id });


                }
                return true;
            });

            //return await new RepositoryFactory().BaseRepository().DeleteAny<CProConBG>(new { id = bgid });
        }

        #endregion
        
        //中科智沃陆玲
        #region 保证金

        //查询项目变更列表
        public static async Task<(IEnumerable<dynamic>, int)> GetHTBZJIndexList(CProConBZJQuerry querry, string strdepids)//string uid
        {
            string str = " ";//and htstatus = '2' 
            if (!(string.IsNullOrEmpty(querry.htbm)))
            {
                str = str + " and htbh like '%" + querry.htbm + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.xmmc)))
            {
                str = str + " and xmmc like '%" + querry.xmmc + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.htlx)))
            {
                str = str + " and htlx = '" + querry.htlx + "'";
            }
            if (!(string.IsNullOrEmpty(querry.htmc)))
            {
                str = str + " and htmc like '%" + querry.htmc + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.bhjeb)))
            {
                str = str + " and bzjje >= '" + querry.bhjeb + "'";
            }
            if (!(string.IsNullOrEmpty(querry.bhjee)))
            {
                str = str + " and bzjje <= '" + querry.bhjee + "'";
            }


            if (!(string.IsNullOrEmpty(querry.jsdw)))
            {
                str = str + " and BuildUnit like ''%" + querry.jsdw + "%'";
            }

            if (!(string.IsNullOrEmpty(querry.status)))
            {
                str = str + " and spstatus = ''" + querry.status + "''";
            }
            if (!(string.IsNullOrEmpty(querry.bzjmc)))
            {
                str = str + " and bzjmc like '%" + querry.bzjmc + "%'";
            }

            //and dblx = '履约银行保函' 
            str = str + " and isnull(step,'0') <> '0'";

            //保证金状态
            //if (!(string.IsNullOrEmpty(querry.bzjzt)))
            //{
            //    str = str + " and bzjzt = '" + querry.bzjzt + "'";
            //}

            str += " and xmid not in (select xmid from CProGD)";

            string sql = "select * from View_ProHTBZJLists where (departid in " + strdepids + " or cqdwid in " + strdepids + ")" + str.Replace("''", "'");

            bool isAsc = false;
            if (querry.sort == "Asc" || querry.sort == "asc")
            {
                isAsc = true;
            }

            if (string.IsNullOrEmpty(querry.column))
            {
                querry.column = "id";
            }
            return await new RepositoryFactory().BaseRepository().FindList(sql, null, querry.column, isAsc, querry.size, querry.current);

        }

        public static async Task<(IEnumerable<dynamic>, int)> GetXZHTBZJIndexList(CProConBZJQuerry querry, string strdepids)//string uid
        {
            string str = " and htstatus = '2' ";//
            if (!(string.IsNullOrEmpty(querry.htbm)))
            {
                str = str + " and htbh like '%" + querry.htbm + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.xmmc)))
            {
                str = str + " and xmmc like '%" + querry.xmmc + "%'";
            }

            if (!(string.IsNullOrEmpty(querry.dxmc)))
            {
                str = str + " and dxmc like '%" + querry.dxmc + "%'";
            }

            if (!(string.IsNullOrEmpty(querry.htlx)))
            {
                str = str + " and htlx = '" + querry.htlx + "'";
            }
            if (!(string.IsNullOrEmpty(querry.htmc)))
            {
                str = str + " and htmc like '%" + querry.htmc + "%'";
            }
            if (!(string.IsNullOrEmpty(querry.bhjeb)))
            {
                str = str + " and bzjje >= '" + querry.bhjeb + "'";
            }
            if (!(string.IsNullOrEmpty(querry.bhjee)))
            {
                str = str + " and bzjje <= '" + querry.bhjee + "'";
            }

            if (!(string.IsNullOrEmpty(querry.jsdw)))
            {
                str = str + " and BuildUnit like '%" + querry.jsdw + "%'";
            }
           
            if (!(string.IsNullOrEmpty(querry.status)))
            {
                str = str + " and spstatus = '" + querry.status + "'";
            }
            if (!(string.IsNullOrEmpty(querry.bzjmc)))
            {
                str = str + " and bzjmc like '%" + querry.bzjmc + "%'";
            }

            //未退还
            str = str + " and dblx = '履约银行保函' and isnull(step,'0') = '0'";

            //并且不是变更记录
            str = str + " and isnull(bzjlog,'') <> 'log'";

            str += " and xmid not in (select xmid from CProGD)";

            //string sql = "select * from(select *,(select xmmc from CProManage where id = (select xmid from dbo.CProManageLX where id = (select dxid from CProContract where CProConBG.htid = CProContract.htid))) xmmc,(select BuildUnit from CProManage where id = (select xmid from dbo.CProManageLX where id = (select dxid from CProContract where CProConBG.htid = CProContract.htid))) BuildUnit,(select departid from CProManage where id = (select xmid from dbo.CProManageLX where id = (select dxid from CProContract where CProConBG.htid = CProContract.htid))) departid,(select dxmc from dbo.CProManageLX where id = (select dxid from CProContract where CProConBG.htid = CProContract.htid)) dxmc from CProConBG ) t where departid in " + await UserRule.GetUserDepid(uid) + str.Replace("''", "'");

            //await UserRule.GetUserDepid(uid)

            //str += " and xmid not in (select xmid from CProGD)";

            string sql = "select * from View_ProHTBZJLists where (departid in " + strdepids + " or cqdwid in " + strdepids + ")" + str;

            bool isAsc = false;
            if (querry.sort == "Asc" || querry.sort == "asc")
            {
                isAsc = true;
            }

            if (string.IsNullOrEmpty(querry.column))
            {
                querry.column = "id";
            }
            return await new RepositoryFactory().BaseRepository().FindList(sql, null, querry.column, isAsc, querry.size, querry.current);

        }

        public async static Task<DataTable> GetHTBZJListByhtid(string tid)
        {
            string strsql = "select * from View_ProHTBZJLists where htid = '" + tid + "'";

            return await new RepositoryFactory().BaseRepository().FindTable(strsql);
        }


        public async static Task<dynamic> GetHTBZJByid(string bgid)
        {
            string strsql = "select * from View_ProHTBZJLists where id = '" + bgid + "'";

            return await new RepositoryFactory().BaseRepository().FindEntity(strsql);

        }

        public async static Task<int> InsertHTBZJ(CProConBZJ htbzj, string ope)
        {
            if (ope == "add")
            {
                return await new RepositoryFactory().BaseRepository().Insert(htbzj);
            }
            else// (ope == "edit")
            {
                return await new RepositoryFactory().BaseRepository().Update(htbzj);
            }

        }

        //删除保证金退还
        public async static Task<bool> DeleteHTBZJTH(Root ids)
        {
            return await TranstionRule.GetRunActionTransAsync(async irp =>
            {
                foreach (BaseDeleteMulti idm in ids.ids)
                {
                    //修改保证金状态
                    CProConBZJ bzj = new CProConBZJ();
                    bzj.id = idm.id;
                    bzj.step = "0";
                    bzj.thje = 0;
                    bzj.status = "";
                    bzj.fjid = "";//这个也置空
                    await irp.Update<CProConBZJ>(bzj);
                }
                return true;
            });
        }

        public async static Task<bool> DeleteHTBZJ(Root ids)
        {
            return await TranstionRule.GetRunActionTransAsync(async irp =>
            {
                foreach (BaseDeleteMulti idm in ids.ids)
                {
                    await irp.DeleteAny<CProConBZJ>(new { id = idm.id });
                }
                return true;
            });

            //return await new RepositoryFactory().BaseRepository().DeleteAny<CProConBG>(new { id = bgid });
        }

        #endregion

        #region 不要
        ////合同保证金列表
        //public static async Task<IEnumerable<dynamic>> GetHTBZJ(string htid)
        //{
        //    string sql = "select * from CProConBZJ where htid = '" + htid + "'";
        //    return await new RepositoryFactory().BaseRepository().FindList(sql, null);
        //}

        ////合同保证金-单条
        //public static async Task<IEnumerable<dynamic>> GetHTBZJById(string keyValue)
        //{
        //    string sql = "select * from dbo.CProConBZJ where id = '" + keyValue + "'";
        //    return await new RepositoryFactory().BaseRepository().FindList(sql, null);
        //}
        ////新增修改保证金
        //public static async Task InsertCProConBZJ(CProConBZJ bzj, string uid)
        //{
        //    CProConBZJ count = await new RepositoryFactory().BaseRepository().FindEntityByKey<CProConBZJ>(bzj.id);
        //    if (count is null)
        //    {
        //        string strid = CommonHelper.GetUniqueId().ToString();
        //        bzj.id = strid;
        //        bzj.createuser = uid;
        //        if ((bzj.thje == bzj.bzjje) && (bzj.bzjje != 0))
        //        {
        //            bzj.status = "已退还";
        //        }
        //        else if (bzj.thje == 0)
        //        {
        //            bzj.status = "未退还";
        //        }
        //        else if (bzj.thje < bzj.bzjje)
        //        {
        //            bzj.status = "部分退还";
        //        }
        //        await new RepositoryFactory().BaseRepository().Insert(bzj);
        //    }
        //    else
        //    {
        //        if ((bzj.thje == bzj.bzjje) && (bzj.bzjje != 0))
        //        {
        //            bzj.status = "已退还";
        //        }
        //        else if (bzj.thje == 0)
        //        {
        //            bzj.status = "未退还";
        //        }
        //        else if (bzj.thje < bzj.bzjje)
        //        {
        //            bzj.status = "部分退还";
        //        }
        //        bzj.createuser = uid;
        //        await new RepositoryFactory().BaseRepository().Update(bzj);
        //        //await new RepositoryFactory().BaseRepository().Update(bzj,true);
        //    }
        //}

        ////删除多条保证金
        //public static async Task DeleteCProConBZJMulti(Root ids)
        //{
        //    foreach (BaseDeleteMulti idm in ids.ids)
        //    {
        //        if (!await BaseEditPower.GetEditPower(idm.id, BaseEditPower.TableName.合同))
        //        {
        //            throw new Exception("请确认数据权限");
        //        }
        //    }

        //    await XMListRule.GetXMSaveTransFuncAsync2(async (iRep) =>
        //    {
        //        foreach (BaseDeleteMulti idm in ids.ids)
        //        {
        //            string delsql = "delete from dbo.CProConBZJ where id = '" + idm.id + "'";

        //            await new RepositoryFactory().BaseRepository().ExecuteSql(delsql);
        //        }
        //        return true;
        //    });

        //    //foreach (BaseDeleteMulti idm in ids.ids)
        //    //{

        //    //}

        //}

        ////归还保证金
        //public static async Task GHCProConBZJ(string keyValue)
        //{
        //    string sql = "UPDATE CProConBZJ SET THJE = BZJJE,THRQ = GETDATE(),STATUS='已退还' where id = '" + keyValue + "'";

        //    await new RepositoryFactory().BaseRepository().ExecuteSql(sql);


        //}


        ////合同付款计划单条
        //public static async Task<IEnumerable<dynamic>> GetHTFKJHById(string keyValue)
        //{
        //    string sql = "select *,(select dxmc from dbo.CProManageLX where id = (select dxid from CProContract where CProContract.htid = CProConFKJH.htid))dxmc,convert(varchar(50), jdnum) + jdunit sjys,(select htje from CProContract where CProContract.htid = CProConFKJH.htid) htje from CProConFKJH where id = '" + keyValue + "'";
        //    return await new RepositoryFactory().BaseRepository().FindList(sql, null);
        //}

        ////新增修改合同付款计划
        //public static async Task InsertCProConFKJH(CProConFKJH fkjh, string uid)
        //{
        //    CProConFKJH count = await new RepositoryFactory().BaseRepository().FindEntityByKey<CProConFKJH>(fkjh.id);
        //    if (count is null)
        //    {

        //        string strid = CommonHelper.GetUniqueId().ToString();
        //        fkjh.id = strid;
        //        fkjh.createuser = uid;
        //        if (string.IsNullOrEmpty(fkjh.status))
        //        {
        //            throw new Exception("请选择付款计划方式");
        //        }
        //        if (fkjh.status == "1")
        //        {
        //            if (string.IsNullOrEmpty(fkjh.jdunit))
        //            {
        //                throw new Exception("节点单位不能为空");
        //            }
        //            if (string.IsNullOrEmpty(fkjh.jdname))
        //            {
        //                throw new Exception("节点名称不能为空");
        //            }
        //            fkjh.status = "1";
        //        }
        //        if (fkjh.status == "2")
        //        {
        //            fkjh.jdname = "已完成工程量";
        //            fkjh.status = "2";
        //        }

        //        await new RepositoryFactory().BaseRepository().Insert(fkjh);
        //    }
        //    else
        //    {

        //        await new RepositoryFactory().BaseRepository().Update(fkjh);
        //    }
        //}

        ////删除多条合同付款计划
        //public static async Task DeleteCProConFKJHMulti(Root ids)
        //{
        //    //foreach (BaseDeleteMulti idm in ids.ids)
        //    //{
        //    //    string delsql = "delete from CProConFKJH where id = '" + idm.id + "'";

        //    //    await new RepositoryFactory().BaseRepository().ExecuteSql(delsql);
        //    //}

        //    await XMListRule.GetXMSaveTransFuncAsync2(async (iRep) =>
        //    {
        //        foreach (BaseDeleteMulti idm in ids.ids)
        //        {
        //            string delsql = "delete from CProConFKJH where id = '" + idm.id + "'";

        //            await new RepositoryFactory().BaseRepository().ExecuteSql(delsql);
        //        }
        //        return true;
        //    });

        //}

        ////查询项目变更列表
        //public static async Task<(DataTable, int)> GetHTBGList(CProConBGQuerry querry, string uid)
        //{
        //    string str = "";
        //    if (!(string.IsNullOrEmpty(querry.Xmmc)))
        //    {
        //        str = str + " and xmmc like '%" + querry.Xmmc + "%'";
        //    }
        //    if (!(string.IsNullOrEmpty(querry.Jsdw)))
        //    {
        //        str = str + " and BuildUnit like ''%" + querry.Jsdw + "'";
        //    }
        //    if (!(string.IsNullOrEmpty(querry.Begindate.ToString())))
        //    {
        //        str = str + " and bgsj >= ''" + querry.Begindate + "''";
        //    }
        //    if (!(string.IsNullOrEmpty(querry.Enddate.ToString())))
        //    {
        //        str = str + " and bgsj <= ''" + querry.Enddate + "''";
        //    }
        //    if (!(string.IsNullOrEmpty(querry.Status)))
        //    {
        //        str = str;
        //    }

        //    string sql = "select * from(select *,(select xmmc from CProManage where id = (select xmid from dbo.CProManageLX where id = (select dxid from CProContract where CProConBG.htid = CProContract.htid))) xmmc,(select BuildUnit from CProManage where id = (select xmid from dbo.CProManageLX where id = (select dxid from CProContract where CProConBG.htid = CProContract.htid))) BuildUnit,(select departid from CProManage where id = (select xmid from dbo.CProManageLX where id = (select dxid from CProContract where CProConBG.htid = CProContract.htid))) departid,(select dxmc from dbo.CProManageLX where id = (select dxid from CProContract where CProConBG.htid = CProContract.htid)) dxmc from CProConBG ) t where departid in " + await UserRule.GetUserDepid(uid) + str.Replace("''", "'");
        //    bool isAsc = true;
        //    if (querry.sort != "Asc" && querry.sort != "asc")
        //    {
        //        isAsc = false;
        //    }
        //    if (string.IsNullOrEmpty(querry.column))
        //    {
        //        querry.column = "id";
        //    }
        //    return await new RepositoryFactory().BaseRepository().FindTable(sql, null, querry.column, isAsc, querry.size, querry.current);

        //}

        ////合同变更列表-根据htid
        //public static async Task<IEnumerable<dynamic>> GetHTBG(string htid)
        //{
        //    string sql = "select *,(select dxmc from dbo.CProManageLX where id = (select dxid from CProContract where CProConBG.htid = CProContract.htid)) dxmc from CProConBG where htid = '" + htid + "'";
        //    return await new RepositoryFactory().BaseRepository().FindList(sql, null);
        //}
        ////合同变更单条
        //public static async Task<IEnumerable<dynamic>> GetHTBGById(string keyValue)
        //{
        //    string sql = "select *,(select dxmc from dbo.CProManageLX where id = (select dxid from CProContract where CProConBG.htid = CProContract.htid)) dxmc from CProConBG where id = '" + keyValue + "'";
        //    return await new RepositoryFactory().BaseRepository().FindList(sql, null);
        //}
        ////新增修改合同变更
        //public static async Task InsertCProConBG(CProConBG bg, string uid)
        //{
        //    if (await GetXMISJUSAsync(bg.htid))
        //    {
        //        throw new Exception("项目已决算，不能操作");
        //    }
        //    CProConBG count = await new RepositoryFactory().BaseRepository().FindEntityByKey<CProConBG>(bg.id);
        //    if (count is null)
        //    {

        //        string strid = CommonHelper.GetUniqueId().ToString();
        //        bg.id = strid;
        //        bg.createuser = uid;
        //        if (string.IsNullOrEmpty(bg.status))
        //        {
        //            bg.status = "2";
        //        }
        //        bg.bgzt2 = "0";
        //        bg.wgzt = "0";
        //        bg.sfba = "0";
        //        bg.bgje = '0';
        //        bg.bghdje = '0';
        //        await new RepositoryFactory().BaseRepository().Insert(bg);
        //    }
        //    else
        //    {

        //        await new RepositoryFactory().BaseRepository().Update(bg);
        //    }
        //}
        ////删除多条合同变更
        //public static async Task DeleteCProConBGMulti(Root ids)
        //{
        //    foreach (BaseDeleteMulti idm in ids.ids)
        //    {
        //        string delsql = "delete from dbo.CProConBG where id = '" + idm.id + "'";

        //        await new RepositoryFactory().BaseRepository().ExecuteSql(delsql);
        //    }

        //}

        #endregion
    }
}
